Oracle PL/SQL
Otázka od: Jiri Lev
25. 9. 2002 17:06
Zdravim vsechny,
Mam nasleduji problem
cursor tabs is SELECT tab_id,tab_name FROM tab1 WHERE tab_id > 3;
m_tab_id tab1.tab_id%TYPE;
m_tab_n tab1.tab_name%TYPE;
cursor unit is SELECT sysop, idname FROM tab2 WHERE idname = p_id;
m_sysop tab2.sysop%TYPE;
m_idname tab2.idname%TYPE;
....
....
open unit;
loop
fetch unit into m_sysop,m_idname;
exit when unit%NOTFOUND;
open tabs;
loop
fetch tabs into m_tab_id,m_tab_n;
exit when tabs%NOTFOUND;
if (m_tab_id < 17) then
delete from m_tab_n where model = m_sysop;
end if;
end loop;
end loop;
close unit;
Na tomhle "delete from m_tab_n where model = m_sysop;" se kompilator
zpouzi a zpouzi...., jestlize mu napisu misto m_tab_n nejakou tabulku
primo, tak to zchroupne.... Nevite nekdo co s tim ? Vsechny tabulky
existuji...
Predem dik Jirka
Odpovedá: Ben, Martin (CAP, GCF)
26. 9. 2002 9:28
Podle verze Oracle zkus jednu z techto moznosti:
1)
declare
v_sql varchar2(255);
begin
v_sql := 'delete from ' || m_tab_n || ' where model = ' || m_sysop;
execute immediate v_sql;
end;
2)
declare
v_cursor number;
v_rows number;
v_sql varchar2(255);
begin
v_cursor := dbms_sql.open_cursor;
v_sql := 'delete from ' || m_tab_n || ' where model = ' || m_sysop;
dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
v_rows := dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
end;
Martin Ben
PS: Poradne si zkontroluj spravny tvar SQL prikazu DELETE v promenne v_sql.
> -----Original Message-----
> From: Jiri Lev [mailto:levj@jhv.cz]
>
> ...
> delete from m_tab_n where model = m_sysop;
> ...
>
> Na tomhle "delete from m_tab_n where model = m_sysop;" se kompilator
> zpouzi a zpouzi...., jestlize mu napisu misto m_tab_n nejakou tabulku
> primo, tak to zchroupne.... Nevite nekdo co s tim ? Vsechny tabulky
> existuji...
Odpovedá: ViragI@logica.com
7. 10. 2002 13:51
Jen tak na prvni pohled je tam duplicitni deklarace m_tab_n. Je tam
deklarovana promena, ktera ma stejne jmeno jako tabulka, nad kterou se
provadi zminovana operace delete.
Ivan Virag
-----Original Message-----
From: Jiri Lev [mailto:levj@jhv.cz]
Sent: Wednesday, September 25, 2002 3:25 PM
To: delphi-l@clexpert.cz
Subject: Oracle PL/SQL
cursor tabs is SELECT tab_id,tab_name FROM tab1 WHERE tab_id > 3;
m_tab_id tab1.tab_id%TYPE;
m_tab_n tab1.tab_name%TYPE;
cursor unit is SELECT sysop, idname FROM tab2 WHERE idname = p_id;
m_sysop tab2.sysop%TYPE;
m_idname tab2.idname%TYPE;
....
....
open unit;
loop
fetch unit into m_sysop,m_idname;
exit when unit%NOTFOUND;
open tabs;
loop
fetch tabs into m_tab_id,m_tab_n;
exit when tabs%NOTFOUND;
if (m_tab_id < 17) then
delete from m_tab_n where model = m_sysop;
end if;
end loop;
end loop;
close unit;
Na tomhle "delete from m_tab_n where model = m_sysop;" se kompilator
zpouzi a zpouzi...., jestlize mu napisu misto m_tab_n nejakou tabulku
primo, tak to zchroupne.... Nevite nekdo co s tim ? Vsechny tabulky
existuji...
Predem dik Jirka
This e-mail and any attachment is for authorised use by the intended
recipient(s) only. It may contain proprietary material, confidential
information and/or be subject to legal privilege. It should not be copied,
disclosed to, retained or used by, any other party. If you are not an intended
recipient then please promptly delete this e-mail and any attachment and all
copies and inform the sender. Thank you.
Odpovedá: Peter Brcko
7. 10. 2002 17:21
Snazis sa o vymaz dynamicky. Takto jednoducho to nepojde. Skus sa pozriet do
package Dbms_Sql.
--------------------------------------------
Ing. Peter Brcko
SoftProjekt s. r. o.
Komenského K-11
069 01 Snina
tel., fax +421 57 762 5395, +421 57 762 3645
pbr@softprojekt.sk; (priv) pbr@post.sk
--------------------------------------------
>-----Original Message-----
>From: Jiri Lev [mailto:levj@jhv.cz]
>Sent: Wednesday, September 25, 2002 3:25 PM
>To: delphi-l@clexpert.cz
>Subject: Oracle PL/SQL
>cursor tabs is SELECT tab_id,tab_name FROM tab1 WHERE tab_id > 3;
>m_tab_id tab1.tab_id%TYPE;
>m_tab_n tab1.tab_name%TYPE;
>cursor unit is SELECT sysop, idname FROM tab2 WHERE idname = p_id;
>m_sysop tab2.sysop%TYPE;
>m_idname tab2.idname%TYPE;
> open unit;
> loop
> fetch unit into m_sysop,m_idname;
> exit when unit%NOTFOUND;
> open tabs;
> loop
> fetch tabs into m_tab_id,m_tab_n;
> exit when tabs%NOTFOUND;
> if (m_tab_id < 17) then
> delete from m_tab_n where model = m_sysop;
> end if;
> end loop;
> end loop;
> close unit;
>Na tomhle "delete from m_tab_n where model = m_sysop;" se kompilator
>zpouzi a zpouzi...., jestlize mu napisu misto m_tab_n nejakou tabulku
>primo, tak to zchroupne.... Nevite nekdo co s tim ? Vsechny tabulky
>existuji...
>
>
>Predem dik Jirka
________
Prva Pomoc, Srandicky, Hry, Hudba, Zoznamenie, Erotika, ...
http://www.post.sk/forum/